import java.sql.*;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JComboBox;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class DbManip {

    static public Connection cnx;
    static public Statement st;
    static public ResultSet rs;
    static public ResultSetMetaData rsmd;
    static public String requete;
    static public boolean ok;
    static public String traderId = "1";
    static public String utilisateur;
    // static public String date = new SimpleDateFormat("dd/MM/yyyy").format(Calendar.getInstance().getTime()).toString();
    static public Timestamp date = new java.sql.Timestamp(Calendar.getInstance().getTime().getTime());
    //static public boolean newData=false;  on peut l'utiliser pour  la mise à jour des donnees dans gestionTrader, gestionClient..
    static public String regexDigit = "\\d+";
    static public String regexLettreNumUnderscore = "^[a-zA-Z]\\w+"; // commencer par une lettre sans espace
    static public String regexFloat = "[0-9]*\\.?[0-9]+";  //"^[0-9]{1,6}\\.[0-9]{2}";
    static public String regexmail = "^[_a-z0-9-]+(\\.[_a-z0-9-]+)*@[a-z0-9-]+(\\.[a-z0-9-]+)+$";
    static public String regexpassword = "^[a-zA-Z]\\w+";
    static public HashCode hash = new HashCode();


    static public void connexion() {
        try {

            System.out.println("Essai d'établissement du driver");
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //  cnx = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "NET_TRADING", "m");
            cnx = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "net_trading", "m");

            System.out.println("connection etablie");
            st = cnx.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            // st = cnx.createStatement();
        }
        catch (ClassNotFoundException | SQLException e) {
            System.out.println("erreur: base introuvable");
            System.out.println(e);
        }
    }

    static public void executer(String requette) {
        try {
            rs = st.executeQuery(requette);
            rsmd = rs.getMetaData();
            System.out.println("La requete: " + requette + "  a été exécutée avec succés !"); //rs != null 
            ok = true;
        }
        catch (SQLException e) {
            System.out.println("La requete: " + requette + "  n' a pas été exécutée !!! ");
            ok = false;

        }
    }

    static public void executerUpdate(String requette) {
        try {
            st.executeUpdate(requette);
            System.out.println("La requete: " + requette + "  a été exécutée avec succés ! ");
            ok = true;
        }
        catch (SQLException e) {
            System.out.println("La requete: " + requette + "  n' a pas été exécutée !!!");
            ok = false;

        }
    }

    // Cette méthode est désignée pour le rs de cette classe car elle fait intervenir l'attribut ok dépendant de rs
    static public void afficher() {
        if (ok == true) {
            try {
                rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    System.out.print(rsmd.getColumnLabel(i) + " ");
                }
                System.out.println("");
                while (rs.next()) { // pointe ligne par ligne      
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        // System.out.println("type =" + rsmd.getColumnTypeName(i));
                        switch (rsmd.getColumnTypeName(i)) {
                            case "NUMBER":
                                System.out.print(rs.getInt(i) + "      ");
                                break;
                            case "CHAR":
                                System.out.print(rs.getString(i) + "      ");
                                break;
                            case "DATE":
                                System.out.println(rs.getDate(i) + "     ");
                                break;
                            case "VARCHAR2":
                                System.out.print(rs.getString(i) + "      ");
                                break;
                        }
                    }
                    System.out.println("");
                }
            }
            catch (SQLException ex) {
                Logger.getLogger(DbManip.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        else {
            System.out.println("le résultat de la requete est null");
        }
    }

    // Cherche l'existence de deux données de deux champs dans une table  
    static public boolean existDsTable(String table, String champ1, String nom1, String champ2, String nom2) throws SQLException {
        boolean exist = false;
        requete = "SELECT * FROM " + table + "  WHERE " + champ1 + "='" + nom1 + "'and " + champ2 + "='" + nom2 + "'";
        System.out.println("requete=    " + requete);
        executer(requete);
        if ((ok) && (rs.next())) {
            System.out.println("existe");
            exist = true;
        }
        return exist;
    }

    static public boolean existChampDsTable(String table, String champ1, String nom1) throws SQLException {
        boolean exist = false;
        requete = "SELECT * FROM " + table + "  WHERE " + champ1 + "='" + nom1 + "'";
        System.out.println("requete=    " + requete);
        executer(requete);
        if ((ok) && (rs.next())) {
            System.out.println("existe");
            exist = true;
        }
        return exist;
    }

    static public void remplirComboBoxADeuxChamps(JComboBox jc, String table, String nom, String prenom, String notice) {
        try {
            jc.addItem("-- " + notice + "--");
            requete = "select " + nom + "," + prenom + " from " + table;
            executer(requete);
            while (rs.next()) {
                String ch1 = rs.getString(1).substring(0, rs.getString(1).indexOf(" "));
                String ch2 = rs.getString(2).substring(0, rs.getString(2).indexOf(" "));
                String ch = ch2 + " " + ch1;
                jc.addItem(ch);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(DbManip.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    static public void remplirComboBoxAUnChamps(JComboBox jc, String table, String nom, String notice) {
        try {
            jc.addItem("-- " + notice + "--");
            requete = "select " + nom + " from " + table;
            System.out.println("requete is " + requete);
            executer(requete);
            while (rs.next()) {
                String ch1 = rs.getString(1).substring(0, rs.getString(1).indexOf("  "));
                System.out.println("ch1= " + ch1 + "long" + ch1.length());
                jc.addItem(ch1);

            }
        }
        catch (SQLException ex) {
            Logger.getLogger(DbManip.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    // Cette fct permet de retourner l id correspondant à une donnée d'un champ donné d'une table donnée
    static public int recupererIdDUnchamp(String table, String champResultat, String champ, String nom) {
        int id = 0;
        try {
            DbManip.requete = "SELECT " + champResultat + " FROM " + table + " WHERE " + champ + "='" + nom + "'";
            DbManip.executer(DbManip.requete);
            while (rs.next()) {
                id = rs.getInt(1);
            }

        }
        catch (SQLException ex) {
            Logger.getLogger(DbManip.class.getName()).log(Level.SEVERE, null, ex);
        }
        return id;

    }

    // Cette fct permet de retourner l id correspondant à une donnée de deux champs donnés d'une table donnée
    static public int recupererIdDedeuxChamps(String table, String champResultat, String champ1, String champ2, String ch) {
        int id = 0;
        try {
            String ch1 = ch.substring(0, ch.indexOf(" "));
            String ch2 = ch.substring(ch.indexOf(" ") + 1);
            DbManip.requete = "SELECT " + champResultat + " FROM " + table + " WHERE " + champ1 + "='" + ch2 + "' and " + champ2 + " ='" + ch1 + "'";
            DbManip.executer(DbManip.requete);
            while (DbManip.rs.next()) {
                id = DbManip.rs.getInt(1);
            }


        }
        catch (SQLException ex) {
            Logger.getLogger(DbManip.class.getName()).log(Level.SEVERE, null, ex);
        }
        return id;
    }

    static public int next_Id(String table, String idTable) {
        int id = 0;
        DbManip.requete = "select max(" + idTable + ") from  " + table;
        DbManip.executer(DbManip.requete);
        try {
            while (DbManip.rs.next()) {
                id = DbManip.rs.getInt(1);
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(DbManip.class.getName()).log(Level.SEVERE, null, ex);
        }

        id++;
        return id;
    }

    static public String recupererChampDUnId(String table, String champResultat, String champDonnee, String nom) {
        String ch = null;
        try {
            DbManip.requete = "SELECT " + champResultat + " FROM " + table + " WHERE " + champDonnee + "='" + nom + "'";
            DbManip.executer(DbManip.requete);
            while (rs.next()) {
                ch = rs.getString(1);
                if (ch.indexOf("  ") != -1) {
                    System.out.println("index=" + ch.indexOf("  "));
                    ch = ch.substring(0, ch.indexOf("  "));
                }
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(DbManip.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ch;
    }

    static public String recupererDeuxChampDUnId(String table, String champResultat1, String champResultat2, String champ, String id) {
        String ch = null;
        try {
            DbManip.requete = "SELECT " + champResultat1 + " ," + champResultat2 + " FROM " + table + " WHERE " + champ + "='" + id + "'";
            DbManip.executer(DbManip.requete);
            // DbManip.
            while (rs.next()) {
                ch = rs.getString(1).substring(0, rs.getString(1).indexOf("  ")) + " " + rs.getString(2).substring(0, rs.getString(2).indexOf("  "));
            }

        }
        catch (SQLException ex) {
            Logger.getLogger(DbManip.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ch;

    }

    static public float ValeurPortefeuille(String id_portefeuille) {
        float val = 0;
        try {

            requete = "SELECT action.VALEURACTUELLE,PORTE_FEUILLE_ACTION.QUANTITY FROM  action INNER JOIN PORTE_FEUILLE_ACTION  ON action.ID_ACTION=PORTE_FEUILLE_ACTION.ID_ACTION WHERE PORTE_FEUILLE_ACTION.ID_PORTE_FEUILLE='" + id_portefeuille + "'";
            executer(requete);
            // afficher();
            while (rs.next()) {
                Float f1 = Float.parseFloat(rs.getString(1));
                Float f2 = Float.parseFloat(rs.getString(2));

                val = val + f1 * f2;


            }

        }
        catch (SQLException ex) {
            Logger.getLogger(DbManip.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("val=" + val);
        return val;
    }
    
    static public void viderListe(JTable table){
        DefaultTableModel model = (DefaultTableModel) table.getModel();
        int n=model.getRowCount();
        for(int i=0;i<n;i++){
            model.removeRow(n-i-1);
        }
    }
}
